package org.jeik.data.services;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.jeik.common.CustomerRequestStatus;
import org.jeik.data.dao.DAO;
import org.jeik.data.dao.DAOFactory;
import org.jeik.data.dao.DaoRecord;
import org.jeik.data.dao.DataException;
import org.jeik.data.models.CustomerRequest;

public class CustomerRequestsService extends ServiceBase {

	private CustomerRequestsService() {
	}

	/**
	 * 
	 * @author AAsha
	 * 
	 */
	private static class SingletonHolder {
		private static CustomerRequestsService instance = new CustomerRequestsService();
	}

	/**
	 * 
	 * @return EmbassyServicesService instance
	 */
	public static CustomerRequestsService getInstance() {
		return SingletonHolder.instance;
	}

	public List<CustomerRequest> loadCustomerRequests() throws DataException {
		DAO dao = DAOFactory.getInstance().createDao();

		String sql = "SELECT * FROM " + Tables.requests.name();
		dao.execute(sql);

		List<CustomerRequest> customerRequests = new ArrayList<CustomerRequest>();
		CustomerRequest request = null;

		for (DaoRecord record : dao.getResult()) {
			request = new CustomerRequest();
			request.setId((Long) record.getValue(Columns.ID.name()));
			request.setFirstName(record.getValue(Columns.FIRST_NAME.name())
					.toString());
			request.setLastName(record.getValue(Columns.LAST_NAME.name())
					.toString());
			request.setIdentificationNumber(record.getValue(
					Columns.IDENTIFICATION_NO.name()).toString());
			request.setMobileNumber(record.getValue(Columns.MOBILE_NO.name())
					.toString());
			request.setServiceType((Long) record.getValue(Columns.SERVICE_TYPE
					.name()));
			request.setStatus((Long) record.getValue(Columns.STATUS.name()));
			request.setNotes(record.getValue(Columns.NOTES.name()).toString());
			request.setCreatedDate((Date) record.getValue(Columns.CREATED_DATE
					.name()));
			request.setLastUpdatedDate((Date) record
					.getValue(Columns.LAST_UPDATED_DATE.name()));

			request.setApplyDate((Date) record.getValue(Columns.APPLY_DATE
					.name()));

			customerRequests.add(request);
		}

		return customerRequests;
	}

	public void addCustomerRequest(CustomerRequest request)
			throws DataException {

		DAO dao = DAOFactory.getInstance().createDao();

		SimpleDateFormat dateFormatter = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");

		String sql = "INSERT INTO " + Tables.requests.name() + " ("
				+ Columns.FIRST_NAME.name() + "," + Columns.LAST_NAME.name()
				+ "," + Columns.IDENTIFICATION_NO.name() + ","
				+ Columns.MOBILE_NO.name() + "," + Columns.SERVICE_TYPE.name()
				+ "," + Columns.STATUS.name() + "," + Columns.NOTES.name()
				+ "," + Columns.APPLY_DATE.name() + ","
				+ Columns.CREATED_DATE.name() + ","
				+ Columns.LAST_UPDATED_DATE.name() + ") VALUES ('"
				+ request.getFirstName() + "','" + request.getLastName()
				+ "','" + request.getIdentificationNumber() + "','"
				+ request.getMobileNumber() + "'," + request.getServiceType()
				+ "," + request.getStatus() + ",'" + request.getNotes() + "','"
				+ dateFormatter.format(request.getApplyDate()) + "','"
				+ dateFormatter.format(request.getCreatedDate()) + "','"
				+ dateFormatter.format(request.getLastUpdatedDate()) + "')";

		

		dao.executeUpdate(sql);

	}

	public void editCustomerRequest(CustomerRequest request)
			throws DataException {

		DAO dao = DAOFactory.getInstance().createDao();

		SimpleDateFormat dateFormatter = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");

		String sql = "UPDATE " + Tables.requests.name() + " SET "
				+ Columns.FIRST_NAME.name() + " = '" + request.getFirstName()
				+ "', " + Columns.LAST_NAME.name() + " = '"
				+ request.getLastName() + "', "
				+ Columns.IDENTIFICATION_NO.name() + " = '"
				+ request.getIdentificationNumber() + "', "
				+ Columns.MOBILE_NO.name() + " = '" + request.getMobileNumber()
				+ "', " + Columns.SERVICE_TYPE.name() + " = "
				+ request.getServiceType() + ", " + Columns.STATUS.name()
				+ " = " + request.getStatus() + ", " + Columns.NOTES.name()
				+ " = '" + request.getNotes() + "', "
				+ Columns.APPLY_DATE.name() + " = '"
				+ dateFormatter.format(request.getApplyDate()) + "' ,"
				+ Columns.LAST_UPDATED_DATE.name() + " = '"
				+ dateFormatter.format(request.getLastUpdatedDate())
				+ "' WHERE " + Columns.ID + " = " + request.getId();

		dao.executeUpdate(sql);

	}

	public List<CustomerRequest> filterCustomersRequests(String firstName,
			String lastName, String mobileNumber) throws DataException {

		DAO dao = DAOFactory.getInstance().createDao();

		String sql = "SELECT * FROM " + Tables.requests.name();

		boolean isWhereUsed = false;

		if (firstName != null && !firstName.trim().equals("")) {

			if (!isWhereUsed) {
				sql += " WHERE ";
				isWhereUsed = true;
			} else {
				sql += " AND ";
			}

			sql += Columns.FIRST_NAME.name() + " = '" + firstName + "'";
		}

		if (lastName != null && !lastName.trim().equals("")) {

			if (!isWhereUsed) {
				sql += " WHERE ";
				isWhereUsed = true;
			} else {
				sql += " AND ";
			}

			sql += Columns.LAST_NAME.name() + " = '" + lastName + "'";
		}

		if (mobileNumber != null && !mobileNumber.trim().equals("")) {

			if (!isWhereUsed) {
				sql += " WHERE ";
				isWhereUsed = true;
			} else {
				sql += " AND ";
			}

			sql += Columns.MOBILE_NO.name() + " = '" + mobileNumber + "'";
		}

		if (!isWhereUsed) {
			return null;
		}

		Calendar cal = Calendar.getInstance();
		cal.add(Calendar.DAY_OF_YEAR, -20);
		Date twentyDaysBefore = cal.getTime();

		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String formattedDate = formatter.format(twentyDaysBefore);

		if (!isWhereUsed) {
			sql += " WHERE ";
			isWhereUsed = true;
		} else {
			sql += " AND ";
		}

		sql += "(" + Columns.STATUS.name() + " != " + CustomerRequestStatus.DONE.getValue()
				+ " OR (  "
				+ Columns.STATUS.name() + " = " + CustomerRequestStatus.DONE.getValue()
				+ " AND "
				+ Columns.LAST_UPDATED_DATE.name() + " > '" + formattedDate
				+ "'  ) )";

		

		dao.execute(sql);

		List<CustomerRequest> customerRequests = new ArrayList<CustomerRequest>();
		CustomerRequest request = null;

		for (DaoRecord record : dao.getResult()) {
			request = new CustomerRequest();
			request.setId((Long) record.getValue(Columns.ID.name()));
			request.setFirstName(record.getValue(Columns.FIRST_NAME.name())
					.toString());
			request.setLastName(record.getValue(Columns.LAST_NAME.name())
					.toString());
			request.setIdentificationNumber(record.getValue(
					Columns.IDENTIFICATION_NO.name()).toString());
			request.setMobileNumber(record.getValue(Columns.MOBILE_NO.name())
					.toString());
			request.setServiceType((Long) record.getValue(Columns.SERVICE_TYPE
					.name()));
			request.setStatus((Long) record.getValue(Columns.STATUS.name()));
			request.setNotes(record.getValue(Columns.NOTES.name()).toString());
			request.setApplyDate((Date) record
					.getValue(Columns.APPLY_DATE.name()));
			request.setCreatedDate((Date) record.getValue(Columns.CREATED_DATE
					.name()));
			request.setLastUpdatedDate((Date) record
					.getValue(Columns.LAST_UPDATED_DATE.name()));
			customerRequests.add(request);
		}

		return customerRequests;

	}

}
